﻿//======================================================
//==     (c)2008 aspxcms inc by NeTCMS v1.0              ==
//==          Forum:bbs.aspxcms.com                   ==
//==         Website:www.aspxcms.com                  ==
//======================================================
using System;
using System.Data;
using System.Data.SqlClient;
using NetCMS.DALFactory;
using NetCMS.Model;
using NetCMS.Common;
using System.Text.RegularExpressions;
using System.Text;
using System.Reflection;
using NetCMS.DALProfile;
using NetCMS.Config;

namespace NetCMS.DALSQLServer
{
    public class Stat : DbBase, IStat
    {
        string isDataBase = NetCMS.Config.UIConfig.indeData;
      
        public DataTable sel()
        {
            string Sql = "select * from " + Pre + "stat_Param";
            DataTable rdr = DbHelper.ExecuteTable(CommandType.Text, Sql, null);
            return rdr;
        }
        public string sel_statInfoById(string id)
        {
            SqlParameter param = new SqlParameter("@id", id);
            string Sql = "Select classname From " + Pre + "stat_class where statid=@id and SiteID='" + NetCMS.Global.Current.SiteID + "'";
            return Convert.ToString(DbHelper.ExecuteScalar(CommandType.Text, Sql, param));
        }
        public bool del_statSql(string ID,int flag)
        {
            #region
            SqlParameter param = new SqlParameter("@ID", ID);
            string Sql = null;
            if (flag == 0)
            {
                Sql = "Delete From " + Pre + "stat_class where statid=@ID and SiteID='" + NetCMS.Global.Current.SiteID + "'";
            }
            else if (flag == 1)
            {
                Sql = "Delete From " + Pre + "stat_Info where classid = @ID and SiteID='" + NetCMS.Global.Current.SiteID + "'";
            }
            else if (flag == 2)
            {
                Sql = "Delete From " + Pre + "stat_Content where classid = @ID and SiteID='" + NetCMS.Global.Current.SiteID + "'";
            }
            int i = DbHelper.ExecuteNonQuery(CommandType.Text, Sql, param);
            if (i == 0)
            {
                return false;
            }
            return true;
            #endregion
        }
        public void Str_InSql(NetCMS.Model.StatParamInfo sp)
        {
            string Sql = "Update " + Pre + "stat_Param Set SystemName=@Str_SystemName,SystemNameE=@Str_SystemNameE,ipCheck=@Str_ipCheck,ipTime=@Str_ipTime,isOnlinestat=@Str_isOnlinestat,pageNum=@Str_pageNum,cookies=@Str_cookies,pointNum=@Str_pointNum,SiteID=@SiteID";
            SqlParameter[] parm = GetStatParamInfo(sp);
            DbHelper.ExecuteNonQuery(CommandType.Text, Sql, parm);
        }

        private SqlParameter[] GetStatParamInfo(NetCMS.Model.StatParamInfo sp)
        {
            SqlParameter[] parm = new SqlParameter[9];
            parm[0] = new SqlParameter("@Str_SystemName", SqlDbType.NVarChar, 100);
            parm[0].Value = sp.SystemName;
            parm[1] = new SqlParameter("@Str_SystemNameE", SqlDbType.NVarChar, 150);
            parm[1].Value = sp.SystemNameE;
            parm[2] = new SqlParameter("@Str_ipCheck", SqlDbType.TinyInt, 1);
            parm[2].Value = sp.ipCheck;
            parm[3] = new SqlParameter("@Str_ipTime", SqlDbType.Int, 4);
            parm[3].Value = sp.ipTime;
            parm[4] = new SqlParameter("@Str_isOnlinestat", SqlDbType.TinyInt, 1);
            parm[4].Value = sp.isOnlinestat;
            parm[5] = new SqlParameter("@Str_pageNum", SqlDbType.Int, 4);
            parm[5].Value = sp.pageNum;
            parm[6] = new SqlParameter("@Str_cookies", SqlDbType.NVarChar, 30);
            parm[6].Value = sp.cookies;
            parm[7] = new SqlParameter("@Str_pointNum", SqlDbType.Int, 4);
            parm[7].Value = sp.pointNum;
            parm[8] = new SqlParameter("@SiteID", SqlDbType.NVarChar, 12);
            parm[8].Value = sp.SiteID;
            return parm;
        }
        public int Stat_Sql()
        {
            int intnum = 20;
            string Sql = "Select pageNum From " + Pre + "stat_Param where SiteID='" + NetCMS.Global.Current.SiteID + "'";//取得参数设置中的每页显示数
            DataTable dt = DbHelper.ExecuteTable(CommandType.Text, Sql, null);
            if (dt != null)
            {
                if (dt.Rows.Count > 0)
                {
                    intnum = int.Parse(dt.Rows[0]["pageNum"].ToString());
                }
                dt.Clear(); dt.Dispose();
            }
            return intnum;
        }
        public void del_statInfoStr(string CheckboxArray,int flag)
        {
            #region
            string Sql = null;
            if (flag == 0)
            {
                Sql = "Delete From " + Pre + "stat_class where statid in ('" + CheckboxArray + "') and SiteID='" + NetCMS.Global.Current.SiteID + "'";
            }
            else if (flag == 1)
            {
                Sql = "Delete From " + Pre + "stat_Info where classid in ('" + CheckboxArray + "') and SiteID='" + NetCMS.Global.Current.SiteID + "'";
            }
            else if (flag == 2)
            {
                Sql = "Delete From " + Pre + "stat_Content where classid in ('" + CheckboxArray + "') and SiteID='" + NetCMS.Global.Current.SiteID + "'";
            }
            DbHelper.ExecuteNonQuery(CommandType.Text, Sql, null);
            #endregion
        }
        public bool del_statInfo(int flag)
        {
            #region
            string Sql = null;
            if (flag == 0)
            {
                Sql = "Delete From " + Pre + "stat_class where SiteID='" + NetCMS.Global.Current.SiteID + "'";
            }
            else if (flag == 1)
            {
                Sql = "Delete From " + Pre + "stat_Info where SiteID='" + NetCMS.Global.Current.SiteID + "'";
            }
            else if (flag == 2)
            {
                Sql = "Delete From " + Pre + "stat_Content where SiteID='" + NetCMS.Global.Current.SiteID + "'";
            }
            int i = DbHelper.ExecuteNonQuery(CommandType.Text, Sql, null);
            if (i == 0)
            {
                return false;
            }
            return true;
            #endregion
        }
        public int sel_statInfo(string Str_statid,int flag)
        {
            #region
            SqlParameter param = new SqlParameter("@statid", Str_statid);
            string Sql = null;
            if (flag == 0)
            {
                Sql = "Select count(statid) From " + Pre + "stat_class where statid = @statid and SiteID='" + NetCMS.Global.Current.SiteID + "'";
            }
            else if (flag == 1)
            {
                Sql = "Select count(classname) From " + Pre + "stat_class Where classname=@statid and SiteID='" + NetCMS.Global.Current.SiteID + "'";
            }
            return (int)DbHelper.ExecuteScalar(CommandType.Text, Sql, param);
            #endregion
        }
        public int insert_statInfo(string Str_statid, string Str_Classname, string SiteID)
        {
            string Sql = "Insert into " + Pre + "stat_class (statid,classname,SiteID) Values('" + Str_statid + "','" + Str_Classname + "','" + SiteID + "')";
            return DbHelper.ExecuteNonQuery( CommandType.Text, Sql, null);
        }
        public int Str_UpdateSql(string Str_ClassnameE, string id)
        {
            SqlParameter[] parm = new SqlParameter[2];
            parm[0] = new SqlParameter("@Str_ClassnameE", SqlDbType.NVarChar,20);
            parm[0].Value = Str_ClassnameE;
            parm[1] = new SqlParameter("@statid", SqlDbType.NVarChar, 12);
            parm[1].Value = id;
            string Sql = "Update " + Pre + "stat_class set classname=@Str_ClassnameE  where statid=@statid and SiteID='" + NetCMS.Global.Current.SiteID + "'";
            return DbHelper.ExecuteNonQuery( CommandType.Text, Sql, parm);
        }
        public int del_Stat(int flag)
        {
            #region
            string Sql = null;
            if (flag == 0)
            {
                Sql = "Delete From " + Pre + "stat_Info where SiteID='" + NetCMS.Global.Current.SiteID + "'";
            }
            else if (flag == 1)
            {
                Sql = "Delete From " + Pre + "stat_content where SiteID='" + NetCMS.Global.Current.SiteID + "'";
            }
            return DbHelper.ExecuteNonQuery(CommandType.Text, Sql, null);
            #endregion
        }
        public DataTable sel_Stat(string viewid, string SiteID,int flag)
        {
            #region
            string Sql = null;
            if (flag == 0)
            {
                Sql = "select vtop,starttime,vhigh,vhightime,today,yesterday from " + Pre + "stat_Content where classid='" + viewid + "' and SiteID='" + SiteID + "'";
            }
            else if (flag == 1)
            {
                Sql = "select vhour,count(id) as allhour from " + Pre + "stat_Info where classid='" + viewid + "' and SiteID='" + SiteID + "' group by vhour";
            }
            else if (flag == 2)
            {
                Sql = "Select top 1 vtime from " + Pre + "stat_Info where classid='" + viewid + "' and SiteID='" + SiteID + "' order by id";
            }
            else if (flag == 3)
            {
                Sql = "select vday,count(id) as allday from " + Pre + "stat_Info where classid='" + viewid + "' and SiteID='" + SiteID + "' group by vday";
            }
            else if (flag == 4)
            {
                Sql = "Select top 1 vtime as vfirst from " + Pre + "stat_Info where classid='" + viewid + "' and SiteID='" + SiteID + "' order by vtime";
            }
            else if (flag == 5)
            {
                Sql = "select vweek,count(id) as allweek from " + Pre + "stat_Info where classid='" + viewid + "' and SiteID='" + SiteID + "' group by vweek";
            }
            else if (flag == 6)
            {
                Sql = "select vmonth,count(id) as allmonth from " + Pre + "stat_Info where classid='" + viewid + "' and SiteID='" + SiteID + "' group by vmonth";
            }
            else if (flag == 7)
            {
                Sql = "select vyear,count(id) as allyear from " + Pre + "stat_Info where classid='" + viewid + "' and SiteID='" + SiteID + "' group by vyear order by vyear DESC";
            }
            else if (flag == 8)
            {
                Sql = "select vpage,count(id) as allpage from " + Pre + "stat_Info where classid='" + viewid + "' and SiteID='" + SiteID + "' group by vpage order by count(id) DESC";
            }
            else if (flag == 9)
            {
                Sql = "select vip,count(id) as allip from " + Pre + "stat_Info where classid='" + viewid + "' and SiteID='" + SiteID + "' group by vip order by count(id) DESC";
            }
            else if (flag == 10)
            {
                Sql = "select vwidth,count(id) as allwidth from " + Pre + "stat_Info where vwidth<>0 and classid='" + viewid + "' and SiteID='" + SiteID + "' group by vwidth order by vwidth DESC";
            }
            else if (flag == 11)
            {
                Sql = "select vwhere,count(id) as allwhere from " + Pre + "stat_Info where classid='" + viewid + "' and SiteID='" + SiteID + "' group by vwhere order by count(id) DESC";
            }
            else if (flag == 12)
            {
                Sql = "select vcome,count(id) as allcome from " + Pre + "stat_Info where classid='" + viewid + "' and SiteID='" + SiteID + "' group by vcome order by count(id) DESC";
            }
            return DbHelper.ExecuteTable(CommandType.Text, Sql, null);
            #endregion
        }
        public DataTable sel_statVip(DateTime newtime, string viewid, string SiteID)
        {
            string Sql ="select vip from " + Pre + "stat_Info where vtime >='" + newtime + "' and classid='" + viewid + "' and SiteID='" + SiteID + "' group by vip";
            return DbHelper.ExecuteTable( CommandType.Text, Sql, null);
        }
        public DataTable sel_yearMonth(int vyear, string viewid, string SiteID,int flag)
        {
            #region
            string Sql = null;
            if (flag == 0)
            {
                Sql = "Select vyear from " + Pre + "stat_Info where vyear=" + vyear + " and  classid='" + viewid + "' and SiteID='" + SiteID + "'";
            }
            else if (flag == 1)
            {
                Sql = "Select vmonth from " + Pre + "stat_Info where vmonth=" + vyear + " and classid='" + viewid + "' and SiteID='" + SiteID + "'";
            }
            return DbHelper.ExecuteTable(CommandType.Text, Sql, null);
            #endregion
        }
        public DataTable sel_vhourcon(int vhour, int vday, int vmonth, int vyear, string viewid, string SiteID)
        {
            string Sql = "Select count(id) as vhourcon from " + Pre + "stat_Info where vhour='" + vhour + "' and vday='" + vday + "' and vmonth='" + vmonth + "' and vyear='" + vyear + "' and classid='" + viewid + "' and SiteID='" + SiteID + "'";
            return DbHelper.ExecuteTable( CommandType.Text, Sql, null);
        }
        public DataTable sel_statCount(int thehour, string vtime, string viewid, string SiteID)
        {
            string Sql = "Select count(id) as vhourcon from " + Pre + "stat_Info where vhour='" + thehour + "' and vtime>'" + vtime + "' and classid='" + viewid + "' and SiteID='" + SiteID + "'";
            return DbHelper.ExecuteTable( CommandType.Text, Sql, null);
        }
        public DataTable sel_vdaycon(string strtheday, string strthetday, string viewid, string SiteID)
        {
            string Sql = "Select count(id) as vdaycon from " + Pre + "stat_Info where vtime>='" + strtheday + "' and vtime<='" + strthetday + "' and classid='" + viewid + "' and SiteID='" + SiteID + "'";
            return DbHelper.ExecuteTable( CommandType.Text, Sql, null);
        }
        public DataTable sel_statById(string strdatetwelve, string viewid, string SiteID,int flag)
        {
            #region
            string Sql = null;
            if (flag == 0)
            {
                Sql = "select vmonth,count(id) as allmonth from " + Pre + "stat_Info where vtime>='" + strdatetwelve + "' and classid='" + viewid + "' and SiteID='" + SiteID + "' group by vmonth";
            }
            else if (flag == 1)
            {
                Sql = "Select count(id) as howsoft from " + Pre + "stat_Info where vsoft='" + strdatetwelve + "' and classid='" + viewid + "' and SiteID='" + SiteID + "'";
            }
            else if (flag ==2)
            {
                Sql = "Select count(id) as howOS from " + Pre + "stat_Info where vOS='" + strdatetwelve + "' and classid='" + viewid + "' and SiteID='" + SiteID + "'";
            }
            return DbHelper.ExecuteTable(CommandType.Text, Sql, null);
            #endregion
        }
        #region 前台调用
        public DataTable sel_StatParamInfo()
        {
            string Sql = "select * from " + Pre + "stat_Param";
            return DbHelper.ExecuteTable( CommandType.Text, Sql, null);
        }
        public DataTable sel_Vip(DateTime newtime)
        {
            SqlParameter param = new SqlParameter("@newtime", newtime);
            string Sql = "select vip from " + Pre + "stat_Info where vtime >=@newtime group by vip";
            return DbHelper.ExecuteTable( CommandType.Text, Sql, param);
        }
        public void Add(StatInfo info)
        {
            string Sql = "insert into " + Pre + "stat_Info(vyear,vmonth,vday,vhour,vtime,vweek,vip,vwhere,vwheref,vcome,vpage,vsoft,vOS,vwidth,classid,SiteID) values(@year,@month,@day,@hour,@time,@week,@ip,@country,@city,@come,@page,@soft,@Os,@width,@statid,@SiteID)";
            SqlParameter[] parm = Getstat_Info(info);
            DbHelper.ExecuteNonQuery( CommandType.Text, Sql, parm);
        }
        private SqlParameter[] Getstat_Info(StatInfo info)
        {
            #region
            SqlParameter[] parm = new SqlParameter[16];
            parm[0] = new SqlParameter("@year", SqlDbType.Int, 4);
            parm[0].Value = info.vyear;
            parm[1] = new SqlParameter("@month", SqlDbType.Int, 4);
            parm[1].Value = info.vmonth;
            parm[2] = new SqlParameter("@day", SqlDbType.Int, 4);
            parm[2].Value = info.vday;
            parm[3] = new SqlParameter("@hour", SqlDbType.Int, 4);
            parm[3].Value = info.vhour;
            parm[4] = new SqlParameter("@time", SqlDbType.NVarChar, 4);
            parm[4].Value = info.vtime;
            parm[5] = new SqlParameter("@week", SqlDbType.Int, 4);
            parm[5].Value = info.vweek;
            parm[6] = new SqlParameter("@ip", SqlDbType.NVarChar, 50);
            parm[6].Value = info.vip;
            parm[7] = new SqlParameter("@country", SqlDbType.NVarChar, 250);
            parm[7].Value = info.vwhere;
            parm[8] = new SqlParameter("@city", SqlDbType.NVarChar, 50);
            parm[8].Value = info.vwheref;
            parm[9] = new SqlParameter("@come", SqlDbType.NVarChar, 250);
            parm[9].Value = info.vcome;
            parm[10] = new SqlParameter("@page", SqlDbType.NVarChar, 250);
            parm[10].Value = info.vpage;
            parm[11] = new SqlParameter("@soft", SqlDbType.NVarChar, 50);
            parm[11].Value = info.vsoft;
            parm[12] = new SqlParameter("@Os", SqlDbType.NVarChar, 50);
            parm[12].Value = info.vOS;
            parm[13] = new SqlParameter("@width", SqlDbType.Int, 4);
            parm[13].Value = info.vwidth;
            parm[14] = new SqlParameter("@statid", SqlDbType.NVarChar, 12);
            parm[14].Value = info.classid;
            parm[15] = new SqlParameter("@SiteID", SqlDbType.NVarChar, 12);
            parm[15].Value = info.SiteID;
            return parm;
            #endregion
        }
        public DataTable sel_stat_content(string statidz)
        {
            SqlParameter param = new SqlParameter("@statidz", statidz);
            string Sql = "select * from " + Pre + "stat_Content where classid=@statidz";
            return DbHelper.ExecuteTable(CommandType.Text, Sql, param);
        }
        public void add_statContent(string vdatee, string starttimee, string highttimee, string statidz, string SiteID)
        {
            string Sql = "Insert into " + Pre + "stat_Content(today,yesterday,vdate,vtop,starttime,vhigh,vhightime,classid,SiteID) Values(1,0,'" + vdatee + "',1,'" + starttimee + "',1,'" + highttimee + "','" + statidz + "','" + SiteID + "')";
            DbHelper.ExecuteNonQuery(CommandType.Text, Sql, null);
        }
        public void Update(int today, int yesterday, string content_data, int all, int heigh, string heightime, string strclassid, string siteID, string strclassids)
        {
            string Sql = "Update " + Pre + "stat_Content set today=" + today + ",yesterday=" + yesterday + ",vdate='" + content_data + "',vtop=" + all + ",vhigh=" + heigh + ",vhightime='" + heightime + "',classid='" + strclassid + "',SiteID='" + siteID + "' where classid='" + strclassid + "'";
        }
        #endregion
    }
}